﻿<cfscript>
/** 
* Senate Frameworks creditFactory
* 
* @hint "The Students' credit request handler." 
*/ 

component 
	displayname="public.service.handler.creditFactory" 
	output=false
	accessors=true
	{
	
	/*PROPERTIES*/
	property name="markAdvice" hint="成绩操作组件";
	
	
	/**
	* @hint "初始化对象"
	*/
	public public.service.handler.creditFactory function init(){
		return this;
	}

	/**
	* @hint "刷新学分以及绩点"
	* 
	* @studentId "学号"
	*/
	public void function creditRefresh( required string studentId ) output=false {
		
		/* 修正课程性质 */
		updateCourseProp( studentId );
		
		/* 刷新课程绩点 */
		creditPointRefresh( studentId );
		
		/* 初始化学分统计数据 */
		initProfile( studentId );
		
		/* 刷新总学分和平均绩点 */
		profileRefresh( studentId );
		
		/* 刷新学位课学分和平均绩点 */
		//degreeRefresh( studentId );

	}

	/**
	* @hint "初始化学生学分统计数据"
	* 
	* @studentId "学号"
	*/
	public void function initProfile( required string studentId ) output=false {
		
		var sql = "";
		var queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 生成单个学生绩点统计数据 */
			sql = "SELECT 1 FROM t_student_profile WHERE stu_id= :studentId";
				
			queryObj = new Query( datasource=application.dnsMaster);
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				
			var rs_check = queryObj.execute( sql=sql ).getResult();
			
			if ( rs_check.recordCount eq 0 ) {
				
				/* 如果没有绩点 则新建绩点统计 */
					sql = "INSERT INTO t_student_profile 
							(
								stu_id, 
								credit, credit_req, credit_opt, credit_phy, credit_com, credit_failure,
								credit_req_pt, credit_req_avgpt, 
								credit_pt, credit_avgpt,
								subject_rank, subject_idx, 
								subject_rank_all, subject_idx_all, 
								credit_grad, credit_grad_pt, credit_grad_avgpt
							) 
							VALUES 
							(
								:studentId, 
								0, 0, 0, 0, 0, 0, 
								0, 0, 
								0, 0, 
								0, 0, 0, 0, 
								0, 0, 0
							) ";
					
				
					queryObj = new Query(datasource = application.dnsMaster);
					
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
					queryObj.execute( sql=sql );
				
			}
			
		}
		
	}
	
	
	/**
	* @hint "修正课程性质"
	* 
	* @studentId "学号"
	*/
	public void function updateCourseProp( required string studentId ) output=false {
		
		var sql = "";
		var queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/*学生年级和专业方向号*/
			sql = "SELECT 
						b.grade, 
						b.sbj_id, 
						t.sbj_direction 
						FROM t_student t 
             			INNER JOIN t_class b ON b.cls_id = t.cls_id 
						WHERE 
							t.stu_id = :studentId ";
				
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			var rs_student = queryObj.execute( sql=sql ).getResult();
			
			if ( rs_student.recordCount ) {
				
				var grade = rs_student.grade;
				var subjectId = rs_student.sbj_id;
				var subjectDir = rs_student.sbj_id & rs_student.sbj_direction;


				/* 修正必修课 */
				sql = "UPDATE t_student_mark a 
						SET 
							a.prop_id = '1' 
						WHERE 
							a.stu_id = :studentId 
							AND 
							(
								a.prop_id < '1' OR a.prop_id > '1' 
							) 
							AND 
							a.cid IN ( 
								SELECT b.cid 
								FROM t_schema b 
								WHERE 
									b.grade = :grade 
									AND 
									( 
										b.sbj_id = :subjectId 
										OR 
										b.sbj_id = :subjectDir 
									) 
									AND 
									b.prop_id = '1' 
							) 
							AND a.mark_mode != '5'"; //老数据，不用修改

				queryObj = new Query( datasource=application.dnsMaster );
		
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="subjectDir", value=subjectDir, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
				queryObj.execute( sql=sql );
			
			
				/* 修正选修课 */
				sql = "UPDATE t_student_mark a 
						SET a.prop_id = '2' 
						WHERE 
							a.stu_id = :studentId 
							AND 
							(
								a.prop_id < '2' OR a.prop_id > '2' 
							) 
							AND 
							a.cid IN ( 
								SELECT b.cid 
								FROM t_schema b 
								WHERE 
									b.grade = :grade 
									AND 
									( 
										b.sbj_id = :subjectId 
										OR 
										b.sbj_id = :subjectDir 
									) 
									AND 
									b.prop_id = '2' 
							)
							AND a.mark_mode != '5'"; //老数据，不用修改
							
				queryObj = new Query( datasource=application.dnsMaster );
		
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="subjectDir", value=subjectDir, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
				queryObj.execute( sql=sql );
			
			
				/* 修正体育课 */
				sql = "UPDATE t_student_mark a 
						SET 
							a.prop_id = '1' 
						WHERE 
							a.stu_id = :studentId 
					   		AND 
							(
								a.prop_id < '1' OR a.prop_id > '1' 
							) 
							AND 
							a.cid IN ( 
								SELECT b.cid 
								FROM t_course b 
								WHERE b.cat_id = 'S2'
							)
							AND a.mark_mode != '5'"; //老数据，不用修改
					
				queryObj = new Query( datasource=application.dnsMaster );
		
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
				queryObj.execute( sql=sql ); 
			

				/* 修正全校通选课 */
				sql = "UPDATE t_student_mark a 
						SET 
							a.prop_id = '3' 
						WHERE 
							a.stu_id = :studentId 
					   		AND 
							(
								a.prop_id < '3' OR a.prop_id > '3' 
							) 
							AND 
							a.cid IN ( 
								SELECT b.cid 
								FROM t_course b 
								WHERE 
									b.cat_id = 'B2' 
									OR 
									b.cat_id = 'S3' 
							)
							AND a.mark_mode != '5'"; //老数据，不用修改
					
				queryObj = new Query( datasource=application.dnsMaster );
		
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
				queryObj.execute( sql=sql ); 
			
				}
			
			}
	}
	
	/**
	* @hint "刷新每个学生课程绩点"
	* 
	* @studentId "学号"
	*/
	public void function creditPointRefresh( required string studentId ) output=false {
		
		var markAdvice = getMarkAdvice();
		
		var sql = "";
		var queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/*=========刷新课程绩点===========*/

			/* 读取学生成绩 */
			sql = "SELECT  
					t.mark_result,
					t.mark_resit,
					t.prop_id,
					t.mark_note,
					t.mark_renote,
					t.mark_mode,
					t.mark_resit_limit,
					b.course_credit,
            	 		t.scm_id, 
					t.credit_point 
					FROM t_student_mark t 
					INNER JOIN t_course b ON b.cid = t.cid 
					WHERE 
						t.stu_id = :studentId 
						AND 
						t.mark_mode != '5'"; //老数据，不用修改";

			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
			var rs_result = queryObj.execute( sql=sql ).getResult(); 
		
			for( var i=1; i lte rs_result.recordCount; i++ ){
			
				var creditPoint = markAdvice.getMarkCredit( rs_result["mark_mode"][i], rs_result["course_credit"][i], rs_result["mark_result"][i], rs_result["mark_resit"][i], rs_result["mark_resit_limit"][i] ); 
				
				if ( creditPoint neq rs_result["credit_point"][i] ) {
				
					/*更新每门课程绩点*/
					sql = "UPDATE 
							t_student_mark a 
							SET 
								a.credit_point = :creditPoint 
							WHERE 
								a.scm_id = :schemaId ";
					
					queryObj = new Query( datasource=application.dnsMaster );
		
					queryObj.addParam( name="creditPoint", value=creditPoint, cfsqltype="cf_sql_decimal", scale=4 );
					queryObj.addParam( name="schemaId", value=rs_result["scm_id"][i], cfsqltype="cf_sql_varchar" );
			
					queryObj.execute( sql=sql ); 	
					
				}
				
			}
			
		}
		
	}

	/**
	* @hint "计算学生学位课绩点"
	* 
	* @studentId "学号"
	*/
	public void function degreeRefresh( required string studentId ) output=false {
		
		writeDump( studnetId );
		abort;//截断，没有学位课，需要时参考
	
		var sql = "";
		var queryObj = "";
		
		/*============计算学位课程平均绩点============*/
		
		/* 单个学生学位课成绩汇总表 */
		var credit = 0; /* 已完成学分 */
		var creditPoint = 0; /* 学位课绩点累计 */
		var allCredit = 0; /* 修读学位课程总学分 */
		var creditPointAvg = 0 ; /* 平均学分绩点 */

		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 读取学生的专业 年级 方向 */
			sql = "	SELECT
						b.grade, b.sbj_id, a.sbj_direction 
						FROM t_student a 
						INNER JOIN t_class b ON a.cls_id = b.cls_id 
						WHERE
							a.stu_id = :studentId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
			var rs_student = queryObj.execute( sql=sql ).getResult();
			
			if ( rs_student.recordCount ) {
				
				/* 专业代码 */
				subjectId = rs_student.sbj_id;
				/* 专业方向 */
				subjectDir = rs_student.sbj_id & rs_student.sbj_direction;
				/* 年级 */
				grade = rs_student.grade;
			
				/* 读取学生课程最高绩点成绩 */
				sql = "SELECT 
							MAX(a.credit_point) credit_point_max, 
							b.course_code, b.course_credit 
							FROM t_student_mark a 
							INNER JOIN t_course b ON b.cid = a.cid 
							WHERE 
								a.stu_id= :studentId 
								AND 
								a.cid IN ( 
									SELECT c.cid FROM t_schema c 
									WHERE 
										c.grade = :grade 
										AND 
										( c.sbj_id = :subjectId OR c.sbj_id = :subjectDir ) 
										AND 
										SUBSTR( c.shm_prop, 1, 1 ) = '1' 
								) 
							GROUP BY 
								b.course_code, b.course_credit ";
							
				queryObj = new Query( datasource=application.dnsMaster);
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectDir", value=subjectDir, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			
				var rs_student_mark = queryObj.execute( sql=sql ).getResult();
				
				
				for ( var a=1; a LTE rs_student_mark.recordCount; a++ ) {
					
					/* 已修读 学分累计 */
					allCredit += rs_student_mark["course_credit"][a];
					/* 已修读 绩点累计 */
					creditPoint += rs_student_mark["credit_point_max"][a];
					
					if ( rs_student_mark["credit_point_max"][a] gt 0 ) {
						/* 已通过总学分累计 */
						credit += rs_student_mark["course_credit"][a];
					}
					
				}
				
				if ( credit gt 0 ) {
					creditPointAvg = creditPoint / allCredit;
				}
				
				
				/* 更新单个学生学位课绩点统计数据 */
				sql = "UPDATE t_student_profile 
						SET 
							credit_grad = :credit, 
							credit_grad_pt = :creditPoint, 
							credit_grad_avgpt = :creditPointAvg 
						WHERE 
							stu_id = :studentId";
					
				queryObj = new Query(datasource = application.dnsMaster);

				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="credit", value=credit, cfsqltype="cf_sql_decimal", scale=2 );
				queryObj.addParam( name="creditPoint", value=creditPoint, cfsqltype="cf_sql_decimal", scale=2 );
				queryObj.addParam( name="creditPointAvg", value=creditPointAvg, cfsqltype="cf_sql_decimal", scale=4 );

				queryObj.execute( sql=sql );

			}
			
		}
		
	}
	
	
	/**
	* @hint "刷新学生各项学分统计和平均绩点"
	* 
	* @studentId "学号"
	*/
	public void function profileRefresh( required string studentId ) output=false {
	
		var sql = "";
		var queryObj = "";

		/*============计算总学分以及总绩点============*/
		
		/* 单个学生成绩汇总表 */
		var credit = 0; /* 总学分 */
		var creditReq = 0; /* 必修课 */
		var creditOpt = 0; /* 限选课 */
		var creditPhy = 0; /* 体育课 */
		var creditCom = 0; /* 通选课 */
		var creditFail = 0; /* 不及格 */
		var creditFailReq = 0; /* 不及格必修课 */
				
		var creditPointReq = 0; /* 必修课学分绩点 */
		var creditPoint = 0; /* 学分绩点 */
				
		/* 计算学生已修读学分 重复课程不累计 */
		var allCredit = 0; /* 修读课程总学分 */
		var allCreditReq = 0; /* 修读必修课总学分 */
		var allCreditPt = 0; /*选修课总学分*/

		var creditPointAvgRequired = 0; /* 必修课平均学分绩点 */
		var creditPointAvg = 0 ; /* 平均学分绩点 */
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
		
			/* 读取学生课程最高绩点成绩 */
			sql = "SELECT 
						a.credit, a.credit_point_o, 
						a.prop_id 
						FROM t_student_mark a 
						WHERE
							a.stu_id= :studentId  ";
							
			queryObj = new Query( datasource=application.dnsMaster);
			
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			var rs_student_mark = queryObj.execute( sql=sql ).getResult();
			
			for ( var b=1; b LTE rs_student_mark.recordCount; b++ ) {
					
				/* 已修读 学分累计 */
				allCredit += rs_student_mark["credit"][b];
				/* 已修读 课程学分绩点累计 */
				creditPoint += rs_student_mark["credit_point_o"][b];

				if ( rs_student_mark["prop_id"][b] eq "1" ) {
					/* 已修读 必修 学分累计 */
					allCreditReq += rs_student_mark["credit"][b];
				}else{
					/* 已修读 选修 学分累计 */
					allCreditPt += rs_student_mark["credit"][b];
				}

					
			}
			/*平均学分绩点*/
			if(allCredit neq 0){
				creditPointAvgRequired = creditPoint / allCredit;
			}
			
		
				
			/* 更新单个学生绩点统计数据 */
			sql = "UPDATE t_student_profile 
					SET 
						credit = :credit, 
						credit_req = :creditReq, 
						credit_opt = :creditOpt,  
						credit_avgpt = :creditPointAvg 
					WHERE 
						stu_id = :studentId";
					
			queryObj = new Query(datasource = application.dnsMaster);
					
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="credit", value=allCredit, cfsqltype="cf_sql_decimal", scale=2 );
			queryObj.addParam( name="creditReq", value=allCreditReq, cfsqltype="cf_sql_decimal", scale=2 );
			queryObj.addParam( name="creditOpt", value=allCreditPt, cfsqltype="cf_sql_decimal", scale=2 );
			queryObj.addParam( name="creditPointAvg", value=creditPointAvgRequired, cfsqltype="cf_sql_decimal", scale=4 );
					
			queryObj.execute( sql=sql );

		
		}
		
	}
	
}

</cfscript>